USE SAS
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetUsersList')
	BEGIN
		DROP  Procedure  dbo.GetUsersList
	END
GO

CREATE Procedure dbo.GetUsersList
	@isActive VARCHAR(1) = NULL
AS
BEGIN

	IF(@isActive IS NOT NULL)
		SET @ISACTIVE = CASE WHEN @ISACTIVE IN ('X','1') THEN '1' ELSE '0' END


	select 
		p.PartnerNo					AS 'User No'
	,	ISNULL(p.PartnerFirstName, '') + ' ' + ISNULL(p.PartnerLastName, '') As [Full Name]
	,	d.DivisionDesc									AS Division		
	,	u.LoginID										AS [Login ID]
	,	(CASE WHEN ISNULL(u.CanCreateParentAcct,0) =1 THEN 'Yes' ELSE 'No' END)		AS [Create Parent Account]
	,	(CASE WHEN ISNULL(u.CanEditProductMaster,0) =1 THEN 'Yes' ELSE 'No' END)	As [Edit Product Master]
	,	(CASE WHEN ISNULL(u.isSuperUser,0) =1 THEN 'Yes' ELSE 'No' END)				AS [Super User]
	,	(CASE WHEN ISNULL(u.isActive,0) =1 THEN 'Yes' ELSE 'No' END)				As [Active]
	,	CAST(p.PartnerGUID AS VARCHAR(40))				AS PKID
	FROM TR_User as u WITH (NOLOCK)
	INNER JOIN TR_Partner as p with (NOLOCK)
		on p.PartnerGUID = u.PartnerGUID
	INNER JOIN TR_PARTNERDIVISION as d with (NOLOCK)
		on d.PartnerDivision = p.PartnerDivision
	WHERE
		u.isActive = ISNULL(@isActive, u.isActive)
	ORDER BY
		u.isActive DESC,
		[Full Name] ASC

END
GO
